﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CMS.Entity;

namespace CMS.DAL
{
    public class ImageManageDAL
    {
        public void Add(ImageManage c)
        {
            string sql = "INSERT INTO [ImageManage] ([Id] ,[Width] ,[Height] ,[Title] ,[Memo] ,[RecTime] ,[ModTime]) VALUES (@Id ,@Width ,@Height ,@Title ,@Memo ,@RecTime ,@ModTime)";
            var sps = SqlHelper.ObjectToSqlParameters(c);
            SqlHelper.ExecuteNonQuery(DBConn.CMS, System.Data.CommandType.Text, sql, sps);
        }

        public void Update(ImageManage c)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("UPDATE [CMS].[dbo].[ImageManage]");
            sb.AppendLine("   SET [Width] = @Width");
            sb.AppendLine("      ,[Height] = @Height");
            sb.AppendLine("      ,[Title] = @Title");
            sb.AppendLine("      ,[Memo] = @Memo");
            sb.AppendLine("      ,[RecTime] = @RecTime");
            sb.AppendLine("      ,[ModTime] = @ModTime");
            sb.AppendLine(" WHERE Id=@Id");
            var sps = SqlHelper.ObjectToSqlParameters(c);
            SqlHelper.ExecuteNonQuery(DBConn.CMS, System.Data.CommandType.Text, sb.ToString(), sps);
        }

        public List<ImageManage> GetLst(int page, int pageSize,out int count)
        {
            //得到所有行
            string sql = "select count(*) from ImageManage";
            count =Convert.ToInt32( SqlHelper.ExecuteScalar(DBConn.CMS, System.Data.CommandType.Text, sql, null));
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("select * from(");
            sb.AppendLine("select row_number() over(order by RecTime desc) as rownumber,* from ImageManage");
            sb.AppendLine(")as cc");
            sb.AppendLine("where rownumber between "+((page-1)*pageSize+1)+" and "+(page*pageSize).ToString());
            return SqlHelper.GetDataByColumnsLst<ImageManage>(DBConn.CMS, sb.ToString(), null);
        }

        public ImageManage GetOne(string id)
        {
            string sql = "select * from ImageManage where id=@id";
            Tools tool = new Tools();
            tool.AddSqlParameter("id", System.Data.SqlDbType.VarChar, id);
            return SqlHelper.GetDataByColumns<ImageManage>(DBConn.CMS, sql, tool.GetSqlParameters());
        }

        public List<ImageManage> GetLst(string title)
        {
            string sql = "select * from ImageManage where title like '%" + title + "%'";
            return SqlHelper.GetDataByColumnsLst<ImageManage>(DBConn.CMS, sql, null);
        }

        public void Delete(string id)
        {
            string sql = "delete from ImageManage where id=@id";
            Tools tool = new Tools();
            tool.AddSqlParameter("id", System.Data.SqlDbType.VarChar, id);
            SqlHelper.ExecuteNonQuery(DBConn.CMS, System.Data.CommandType.Text, sql, tool.GetSqlParameters());
        }
    }
}
